In this exercise, you will create a simple Windows Azure application to manipulate the data in the Customer table of the AdventureWorksLT2008 database.

The purpose of this exercise is to demonstrate just how simple it is to work with SQL Azure and Windows Azure using the graphical Visual Studio 'drag and drop' approach.

Task 1 – Loading the Sample Database into SQL Azure

1. Connect to the HoLTestDB database on your SQL Azure server using the HoLUser login that you created in the previous exercises. You can use either SQL Server Management Studio or the Database Manager for SQL Azure to perform these steps.
2. If you are using SQL Server Management Studio, in the File menu, select Open | File and then navigate to the Assets folder inside the Source folder of this lab; if you use the Database Manager for SQL Azure, select the Database context on the upper left corner and click the Open Query button in the File group. Select the AdventureWorks2008LT_Azure.sql file and click Open.

   Note:
   This script contains a cleaned up export script from the AdventureWorksLT2008 sample database available for download in the Codeplex sample databases project site. SQL Azure sample databases will be available for downloading that you could use to replace this script file.

   
   
3. Execute the query. This may take a few minutes, as you are creating a subset of the Adventure Works database.

Task 2 – Creating the Visual Studio Project

In this task, you create a new Visual Studio project for a Windows Azure Web Site.

1. Open Microsoft Visual Studio 2010 in elevated administrator mode. To do this, in Start | All Programs | Microsoft Visual Studio 2010, right-click the Microsoft Visual Studio 2010 shortcut and choose Run as Administrator.
2. If the User Account Control dialog appears, click Continue.
3. From the File menu, choose New and then Project.
4. In the New Project dialog, expand the language of your preference (Visual C# or Visual Basic) in the Installed Templates list and select Cloud.
5. In the Templates list, select Windows AzureProject. Set the name of the project to “AdventureWorks” and the location inside Ex3-BuildingSQLAzureApp in the Source folder of the lab. Ensure that Create directory for solution is checked and then set the name of the solution to “Begin”. Click OK to create the project.

   

   Figure 33
   Creating a new Web Cloud Service (C#)

   
   

   

   Figure 34
   Creating a new Web Cloud Service (Visual Basic)

   
   
6. In the New Windows Azure Project dialog, inside the Roles panel, expand the tab for the language of your choice (Visual C# or Visual Basic), select ASP.NET Web Role from the list of available roles and click the right arrow (>) to add an instance of this role to the solution. Before closing the dialog, select the new role in the right panel, click the pencil icon and rename the role as AdventureWorksWeb. Click OK to create the cloud service solution.

   

   Figure 35
   Adding a Web Role to the Solution (C#)

   
   

   

   Figure 36
   Adding a Web Role to the Solution (VB)

   
   
7. When the project template has finished creating items, you should be presented with the Default.aspx page. If not, open this file.
8. Ensure that you are viewing the Default.aspx page in Design View and click the Design button.
9. Drag and drop a GridView control from the Datasection of the Toolbox onto the design canvas.

   

   Figure 37
   Adding a GridView control

   
   
10. From the SmartTagon the upper right corner of the newly created GridView, choose the New data source option on the Choose Data Source combo box.

    

    Figure 38
    Creating a new data source

    
    
11. In the Data Source Configuration Wizard, choose a data source type of Database and leave the default ID. Click OK.

    

    Figure 39
    Choosing a Data Source

    
    
12. In the Configure Data Source dialog, click New Connection.

    

    Figure 40
    Creating a new Connection

    
    
13. If prompted by a Choose data source dialog, select Microsoft SQL Server and click Continue.
14. Now, configure a connection to your SQL Azure database. In the Add Connection dialog, ensure your provider is Microsoft SQL Server (SqlClient) selecting Microsoft SQL Server inside Data Source list and .NET Framework Data Provider for SQL Server in the Data Provider combo. Then set the Server name to the name of the server for your SQL Azure subscription. Next, change the authentication type to Use SQL Server Authentication and type the credentials for your SQL Azure subscription. Finally, enter HoLTestDB in the database name drop down list.

    

    Figure 41
    Configuring a connection to the HolTestDB database in SQL Azure

    
    
15. Press Test Connection. If the connection information is correct, you should receive a dialog indicating success. Click OK to proceed.

    

    Figure 42
    Confirmation of a successful connection

    
    
16. Click OK to close the Add Connection dialog.
17. Click Next to proceed with the Data Source Configuration Wizard.
18. Ensure that the option labeled Yes, save this connection as is checked, set the name of the connection to AdventureWorksLTConnectionString, and then click Next.

    

    Figure 43
    Saving the connection string in the application configuration file

    
    
19. Select the option labeled Specify a custom SQL statement or stored procedure and then click Next.

    

    Figure 44
    Using a custom SQL statement to query the database

    
    

    Note:
    You cannot use the Specify columns from a table or view option because AdventureWorks uses a named Schema (SalesLT) that you need to explicitly reference.

    
    
20. Paste the following statement into the SQL Statement box and click Next.

    T-SQL	Copy Code
    SELECT [FirstName], [LastName], [CompanyName], [EmailAddress] FROM [SalesLT].[Customer]

    
    

    

    Figure 45
    Defining a custom SQL statement

    
    
21. Press Test Query and you should see results returned.

    

    Figure 46
    Testing the query against the database

    
    
22. Click Finish.
23. Press F5 to run the application in the compute emulator.
24. The application will execute and you will see the list of all customers in the browser:
    

    

    Figure 47
    Retrieving a list of customers from the database

    
    
25. Close the browser window.